#!/usr/bin/env python3
# -*- coding: utf-8 -*-

# Author : Mike Tung
# License: MIT License
# File   : mysql.py
# Date   : 2021-06-15 14-09
# Version: 0.1
# Description: connection mysql manager.

from conf.settings import *
import pymysql
from common import logger
log = logger.tlog()
logger = log.define()


class MySQLCommand(object):
    # def __init__(self,host,port,user,passwd,db,table):
    def __init__(self, dbinfo):
        self.host = dbinfo['HOST']
        self.port = dbinfo['PORT']
        self.user = dbinfo['USER']
        self.password = dbinfo['PASSWORD']
        self.db = dbinfo['DATABASE']
        self.table = dbinfo['TABLE']

    def connectMysql(self):
        try:
            # logger.info(self.port)
            self.conn = pymysql.connect(host=self.host, port=self.port, user=self.user, passwd=self.password,
                                        db=self.db, charset='utf8')
            self.cursor = self.conn.cursor()
            # logger.info("connect sucess!")
        except:
            print('connect mysql error.')

    # def executeMysql(self, sql):
    #     try:
    #         # logger.info(sql)
    #         self.cursor.execute(sql)
    #         self.conn.commit()
    #     except Exception as e:
    #         logger.error('SQLExecuteFailed:' + sql)
    #         logger.error(e)

    def executeBuleMysql(self, sql, list):
        try:
            self.cursor.execute(sql, list)
            self.conn.commit()
        except Exception as e:
            logger.error('SQLExecuteFailed:' + sql)
            logger.error(e)

    def queryMysql(self, sql):
        # sql = "SELECT * FROM " + self.table
        try:
            self.conn.ping()
            self.cursor.execute(sql)
            row = self.cursor.fetchall()
            return row
        except:
            logger.info(sql + ' execute failed.')


    def insertMysql(self, id, name, sex):
        sql = "INSERT INTO " + self.table + " VALUES(" + id + "," + "'" + name + "'," + "'" + sex + "')"

        try:
            self.conn.ping()
            self.cursor.execute(sql)
        except:
            print("insert failed.")

    def updateMysqlSN(self, id, name, sex):
        sql = "UPDATE " + self.table + " SET sex='" + sex + "'" + " WHERE name='" + name + "'"
        print("update sn:" + sql)
        try:
            self.conn.ping()
            self.cursor.execute(sql)
            self.conn.commit()
        except:
            self.conn.rollback()

    def closeMysql(self):
        self.cursor.close()
        self.conn.close()

if __name__ == '__main__':
    cur = MySQLCommand(DBConfig['userdb'])
    cur.connectMysql()
    sql = 'SELECT * FROM company_info'
    a = cur.queryMysql(sql)
    logger.info(a)
    cur.closeMysql()

